# Databricks notebook source
# MAGIC %md
# MAGIC # Installations

# COMMAND ----------

# MAGIC %pip install -U mostlyai[local] 
# MAGIC %pip install ipywidgets
# MAGIC %restart_python

# COMMAND ----------

import pandas as pd
from mostlyai.sdk import MostlyAI

mostly = MostlyAI(local=True)

# COMMAND ----------

# MAGIC %md
# MAGIC # Read and process tables

# COMMAND ----------

# MAGIC %md
# MAGIC ### Interopko

# COMMAND ----------

from pyspark.sql.functions import col, lit, concat

df_interopko=spark.table("admin_govern_sta_des.tmp_mostlyai.interopko")
df_interopko=(df_interopko
         .withColumn("pk", concat(col("Usuari_solicitant"), lit("_"),col("N_Expedient"), lit("_"), col("N_Procediment"))))
display(df_interopko.limit(5))

# COMMAND ----------

display(df_interopko.select("Tipus_interop").groupBy("Tipus_interop").count())

# COMMAND ----------

# MAGIC %md
# MAGIC ### Interopok

# COMMAND ----------

df_interopok = spark.table("admin_govern_sta_des.tmp_mostlyai.interopok")
df_interopok=(df_interopok
         .withColumn("pk", concat(col("Usuari_solicitant"), lit("_"),col("N_Expedient"), lit("_"), col("N_Procediment")))
         )
display(df_interopok)

# COMMAND ----------

display(df_interopok.select("Tipus_interop").groupBy("Tipus_interop").count())

# COMMAND ----------

# MAGIC %md
# MAGIC ### Fullquery

# COMMAND ----------

df_fullquery = spark.table("admin_govern_sta_des.tmp_mostlyai.fullquery")
df_fullquery=(df_fullquery
         .withColumn("pk", concat(col("identificador"), lit("_"),col("N_Expedient"), lit("_"), col("N_Procediment"))))
display(df_fullquery.limit(20))

# COMMAND ----------

# MAGIC %md
# MAGIC ### Pagament Nomina

# COMMAND ----------

from pyspark.sql.functions import col, regexp_replace, cast, lit, concat
df_pagamentnomina=spark.table("admin_govern_sta_des.tmp_mostlyai.pagamentnomina")
df_pagamentnomina=(df_pagamentnomina
                   .withColumn("Import", regexp_replace(regexp_replace(col("Import"), "\.", ""), ",", ".").cast("double"))
                   .withColumn("pk", concat(col("Usuari_solicitant"), lit("_"),col("N_Expedient"), lit("_"), col("N_Procediment"))))
display(df_pagamentnomina.limit(5))

# COMMAND ----------

# MAGIC %md
# MAGIC ### Actuacions nomina

# COMMAND ----------

from pyspark.sql.functions import concat, col, lit

df_actuacionsnomina_raw=spark.table("admin_govern_sta_des.tmp_mostlyai.actuacionsnomina")
df_fullquery_=spark.table("admin_govern_sta_des.tmp_mostlyai.fullquery").select("identificador", "N_Expedient", "N_Procediment").distinct()

df_actuacionsnomina = (
    df_actuacionsnomina_raw.join(df_fullquery_, on=["N_Expedient", "N_Procediment"], how="left")
    .withColumn("pk", concat(col("identificador"), lit("_"),col("N_Expedient"), lit("_"), col("N_Procediment")))
    .select("pk", "identificador", "N_Expedient", "N_Procediment", "Data_actuacio", "Efecte_Nomina")
)

display(df_actuacionsnomina.limit(5))

# COMMAND ----------

# MAGIC %md
# MAGIC ### Relationship

# COMMAND ----------

from pyspark.sql.functions import col, lit, concat

df_interopko_renamed = df_interopko.withColumnRenamed('Usuari_solicitant', 'identificador')
df_interopok_renamed = df_interopok.withColumnRenamed('Usuari_solicitant', 'identificador')
df_pagamentnomina_renamed = df_pagamentnomina.withColumnRenamed('Usuari_solicitant', 'identificador')

df_relationship = (
  df_interopko_renamed.select(['identificador', 'N_Expedient', 'N_Procediment'])
            .union(df_interopok_renamed.select(['identificador', 'N_Expedient', 'N_Procediment']))
            .union(df_fullquery.select(['identificador', 'N_Expedient', 'N_Procediment']))
            .union(df_pagamentnomina_renamed.select(['identificador', 'N_Expedient', 'N_Procediment'])) 
            .select("*")
            .distinct()
            .withColumn("pk", concat(col("identificador"), lit("_"),col("N_Expedient"), lit("_"), col("N_Procediment")))
            .withColumn("fk", concat(col("identificador"), lit("_"),col("N_Expedient")))
            .select("pk", "fk", "N_Procediment", "identificador")
            )
display(df_relationship.select("pk").distinct())

# COMMAND ----------

display(df_relationship.limit(5))

# COMMAND ----------

# MAGIC %md
# MAGIC ### Solicitud

# COMMAND ----------

from pyspark.sql.functions import col, lit, concat
df_solicitud=spark.table("admin_govern_sta_des.tmp_mostlyai.solicitud")
df_solicitud=(df_solicitud
              .drop("_c166")
              .withColumn("fk", concat(col("solicitant1Identificador"), lit("_"),col("expedient"))))
display(df_solicitud.limit(5))

# COMMAND ----------

# MAGIC %md
# MAGIC ### Titular

# COMMAND ----------

df_titular=(
  spark.table("admin_govern_sta_des.tmp_mostlyai.titular")
    .withColumnRenamed("identificador", "solicitant1Identificador")
            )
display(df_titular.limit(5))

# COMMAND ----------

df_titular.count()

# COMMAND ----------

# MAGIC %md
# MAGIC ### Esborrany

# COMMAND ----------

df_esborrany = spark.table("admin_govern_sta_des.tmp_mostlyai.esborrany").withColumnRenamed("identificador_document", "solicitant1Identificador")
display(df_esborrany)

# COMMAND ----------

# MAGIC %md
# MAGIC # Generator

# COMMAND ----------

# MAGIC %md
# MAGIC ## Configuration

# COMMAND ----------

from mostlyai.sdk.domain import ModelEncodingType

relationship_table_config = {
    "name": "relationship",
    "data": df_relationship,
    "tabular_model_configuration": {
        "max_training_time": 45
    },
    "primary_key": "pk",
    "foreign_keys": [{"column": "fk", "referenced_table": "solicitud", "is_context": True}],

    "columns": [{"name" : "pk", "model_encoding_type": ModelEncodingType.auto},
                {"name" : "fk", "model_encoding_type": ModelEncodingType.auto},
                {"name" : "N_Procediment", "model_encoding_type": ModelEncodingType.tabular_character},
                {"name" : "identificador", "model_encoding_type": ModelEncodingType.tabular_character}
                ]
}

solicitud_table_config = {
    "name": "solicitud",
    "data": df_solicitud,
    "tabular_model_configuration": {
        "max_training_time": 240
    },
    "primary_key": "fk",
    "foreign_keys": [{"column": "solicitant1Identificador", "referenced_table": "titular", "is_context": False}],
    "columns": [
      {"name" : "fk", "model_encoding_type": ModelEncodingType.auto},
      {"name": "canalEntrada", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "usuariIniciador", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "solicitant1Identificador", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "enviamentAny", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "enviamentMes", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "enviamentDia", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "presentador", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "prestacio", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "dataRegistre", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "dataInici", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "solicitant1Genere", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "expedient", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "sollicitant1IdentificadorTipus", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "sollicitant1Nom", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "sollicitant1CognomPrimer", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "sollicitant1CognomSegon", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "sollicitant1NomSentit", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "sollicitant1NaixementData", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "sollicitant1DniNacionalitzat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "sollicitant1DniDataExpedicioDNI", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "sollicitant1DniNieAnterior", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "sollicitant1NieDataCaducitat", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "sollicitant1NieDataPeticioRenovacio", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "sollicitant1NieTipusPermisResidencia", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "sollicitant1EstatCivil", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "sollicitant1Nacionalitat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "notificacioTipus", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "notificacioCanalAvis", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "telefonMobil", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "correuElectronic", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "telefonFix", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "residenciaTipusVia", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "residenciaNomVia", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "residenciaNumero", "model_encoding_type": ModelEncodingType.tabular_numeric_auto},
      {"name": "residenciaQualificador", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "residenciaBloc", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "residenciaEscala", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "residenciaPis", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "residenciaPorta", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "residenciaCodiPostal", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "residenciaPoblacio", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "residenciaAmpliacioAdreca", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "residenciaTipusDomicili", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "residenciaTipusLlar", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "residenciaRegimTinenca", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "residenciaNormalitzada", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "notificacioTipusVia", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "notificacioNomVia", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "notificacioNumero", "model_encoding_type": ModelEncodingType.tabular_numeric_auto},
      {"name": "notificacioQualificador", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "notificacioBloc", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "notificacioEscala", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "notificacioPis", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "notificacioPorta", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "notificacioCodiPostal", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "notificacioPoblacio", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "notificacioAmpliacioAdreca", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "notificacioNormalitzada", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "difuntTipusIdentificador", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "difuntIdentificador", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "difuntNom", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "difuntCognomPrimer", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "difuntCognomSegon", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "difuntNaixementData", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "difuntGenere", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "difuntRelacioAmbSollicitant", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "difuntConviviaAmbSollicitant", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "pensioEstrangerDataInici", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "pensioEstrangerImport", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "pensioEstrangerPeriodicitat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "pensioEstrangerPagues", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "pensioEstrangerPagador", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "pensioPlaPrivatDataInici", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "pensioPlaPrivatImport", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "pensioPlaPrivatPeriodicitat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "pensioPlaPrivatPagues", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "pensioPlaPrivatPagador", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "pensioCompensatoriaDataInici", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "pensioCompensatoriaImport", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "pensioCompensatoriaPeriodicitat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "pensioCompensatoriaPagues", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "pensioCompensatoriaPagador", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "sollicitant1CondicioCatalaRetornat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "sollicitant1CondicioCatalaRetornatAcreditat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "activitatLaboralDataInici", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "activitatLaboralDataFi", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "activitatLaboralImportBrutAnual", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "activitatLaboralPerCompte", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "activitatLaboralPagador", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "residit10Anys", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "residit2DarrersAnys", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "periodesResidenciaHiHa", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "guardaCustodiaInfant", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "guardaCustodiaInfantAcreditat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "unicProgenitor", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "familiaNombrosa", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "familiaNombrosaACatalunya", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "familiaMonoparental", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "violenciaGenere", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "violenciaGenereAcreditat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "progenitorPrivacioLlibertat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "progenitorPrivacioLlibertatQuin", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "sollicitant2IdentificadorTipus", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "sollicitant2Identificador", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "sollicitant2DniNacionalitzat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "sollicitant2DniDataExpedicioDNI", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "sollicitant2DniNieAnterior", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "sollicitant2NieDataCaducitat", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "sollicitant2NieDataPeticioRenovacio", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "sollicitant2Nom", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "sollicitant2CognomPrimer", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "sollicitant2CognomSegon", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "sollicitant2NaixementData", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "sollicitant2Genere", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "sollicitant2EstatCivil", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "sollicitant2Nacionalitat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "sollicitant2RelacioAmbSollicitant1", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "sollicitant2CondicioCatalaRetornat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "fillMesUn", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "fillsQuants", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant1IdentificadorTipus", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant1Identificador", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "infant1Nom", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "infant1CognomPrimer", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "infant1CognomSegon", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "infant1NaixementData", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "infant1Genere", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant1FiliacioTipus", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant1FiliacioData", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "infant1FiliacioAcreditada", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant1AcollimentCatalunya", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant1AcollimentProvisional", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant1NaixementPais", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant1NaixementInscrit", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant1NaixementInscritRegistre", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant1NaixementInscritConsolat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant2IdentificadorTipus", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant2Identificador", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "infant2Nom", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "infant2CognomPrimer", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "infant2CognomSegon", "model_encoding_type": ModelEncodingType.tabular_character},
      {"name": "infant2NaixementData", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "infant2Genere", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant2FiliacioTipus", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant2FiliacioData", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "infant2FiliacioAcreditada", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant2AcollimentCatalunya", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant2AcollimentProvisional", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant2NaixementPais", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant2NaixementInscrit", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant2NaixementInscritRegistre", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "infant2NaixementInscritConsolat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "personaRefugiada", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "personaRefugiadaAcreditat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "CIP", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "situacioImv", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "situacioImvAcreditat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "situacioImvNumSollicitud", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "situacioImvDataPresentacio", "model_encoding_type": ModelEncodingType.tabular_datetime},
      {"name": "situacioImvImport", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "valoratDiscapacitat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "valoratDiscapacitatAcreditat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "grauDiscapacitat", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "pensioCompensatoriaDret", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "pensioCompensatoriaRep", "model_encoding_type": ModelEncodingType.tabular_categorical},
      {"name": "pensioCompensatoriaAmbReclamacio", "model_encoding_type": ModelEncodingType.tabular_categorical}]
}

titular_table_config = {
    "name": "titular",
    "data": df_titular,
    "tabular_model_configuration": {
        "max_training_time": 45
    },
    "primary_key": "solicitant1Identificador",
    "columns": [{"name" : "identificadorTipus", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "solicitant1Identificador", "model_encoding_type": ModelEncodingType.auto},
                {"name" : "identificadorNieAnterior", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "nom", "model_encoding_type": ModelEncodingType.tabular_character},
                {"name" : "cognomPrimer", "model_encoding_type": ModelEncodingType.tabular_character},
                {"name" : "cognomSegon", "model_encoding_type": ModelEncodingType.tabular_character},
                {"name" : "nomSentit", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "naixementData", "model_encoding_type": ModelEncodingType.tabular_datetime},
                {"name" : "genere", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "estatCivil", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "nacionalitat", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "situacioPersona", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "residenciaTipusVia", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "residenciaNomVia", "model_encoding_type": ModelEncodingType.tabular_character},
                {"name" : "residenciaNumero", "model_encoding_type": ModelEncodingType.tabular_numeric_auto},
                {"name" : "residenciaQualificador", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "residenciaBloc", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "residenciaEscala", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "residenciaPis", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "residenciaPorta", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "residenciaCodiPostal", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "residenciaPoblacio", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "residenciaAmpliacioAdreca", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "residenciaTipusDomicili", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "telefonMobil", "model_encoding_type": ModelEncodingType.tabular_character},
                {"name" : "telefonFix", "model_encoding_type": ModelEncodingType.tabular_character},
                {"name" : "correuElectronic", "model_encoding_type": ModelEncodingType.tabular_character},
                {"name" : "notificacioTipusVia", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "notificacioNomVia", "model_encoding_type": ModelEncodingType.tabular_character},
                {"name" : "notificacioNumero", "model_encoding_type": ModelEncodingType.tabular_numeric_auto},
                {"name" : "notificacioQualificador", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "notificacioBloc", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "notificacioEscala", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "notificacioPis", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "notificacioPorta", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "notificacioCodiPostal", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "notificacioPoblacio", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "notificacioAmpliacioAdreca", "model_encoding_type": ModelEncodingType.tabular_categorical}]
}

esborrany_table_config = {
    "name": "esborrany",
    "data": df_esborrany,
    "tabular_model_configuration": {
        "max_training_time": 45
    },
    "foreign_keys": [{"column": "solicitant1Identificador", "referenced_table": "titular", "is_context": True}],
    "columns": [{"name" : "solicitant1Identificador", "model_encoding_type": ModelEncodingType.auto},
                {"name" : "nommunicipi", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "any_creacio", "model_encoding_type": ModelEncodingType.tabular_numeric_auto},
                {"name" : "mes_creacio", "model_encoding_type": ModelEncodingType.tabular_numeric_auto},
                {"name" : "dia_creacio", "model_encoding_type": ModelEncodingType.tabular_numeric_auto},
                {"name" : "prestacio", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "diferida", "model_encoding_type": ModelEncodingType.tabular_categorical}]
}

interopko_table_config = {
    "name": "interopko",
    "data": df_interopko,
    "tabular_model_configuration": {
        "max_training_time": 45
    },
    "foreign_keys": [{"column": "pk", "referenced_table": "relationship", "is_context": True}],
    "columns": [{"name" : "pk", "model_encoding_type": ModelEncodingType.auto},
                #{"name" : "Usuari_solicitant", "model_encoding_type": ModelEncodingType.tabular_categorical},
                #{"name" : "N_Expedient", "model_encoding_type": ModelEncodingType.tabular_categorical},
                #{"name" : "N_Procediment", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Data_crida", "model_encoding_type": ModelEncodingType.tabular_datetime},
                {"name" : "Tipus_expedient", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Tipus_procediment", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Tipus_interop", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Estat_interop", "model_encoding_type": ModelEncodingType.tabular_categorical}]
}

interopok_table_config = {
    "name": "interopok",
    "data": df_interopok,
    "tabular_model_configuration": {
        "max_training_time": 45
    },
    "foreign_keys": [{"column": "pk", "referenced_table": "relationship", "is_context": True}],
    "columns": [{"name" : "pk", "model_encoding_type": ModelEncodingType.auto},
                #{"name" : "Usuari_solicitant", "model_encoding_type": ModelEncodingType.tabular_categorical},
                #{"name" : "N_Expedient", "model_encoding_type": ModelEncodingType.tabular_categorical},
                #{"name" : "N_Procediment", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Data_crida", "model_encoding_type": ModelEncodingType.tabular_datetime},
                {"name" : "Tipus_expedient", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Tipus_procediment", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Tipus_interop", "model_encoding_type": ModelEncodingType.tabular_categorical}]
}

fullquery_table_config = {
    "name": "fullquery",
    "data": df_fullquery,
    "tabular_model_configuration": {
        "max_training_time": 90
    },
    "foreign_keys": [{"column": "pk", "referenced_table": "relationship", "is_context": True}],
    "columns": [{"name" : "pk", "model_encoding_type": ModelEncodingType.auto},
                #{"name" : "identificador", "model_encoding_type": ModelEncodingType.tabular_categorical},
                #{"name" : "N_Expedient", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Tipus_Expedient", "model_encoding_type": ModelEncodingType.tabular_categorical},
                #{"name" : "N_Procediment", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Tipus_Procediment", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Usuari_Iniciador", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Situacio_Procediment", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Motiu_Situacio_Procediment", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Estat_Calculat_Procediment", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Terminacio_Procediment", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Data_Registre", "model_encoding_type": ModelEncodingType.tabular_datetime},
                {"name" : "Descripcio_tramit", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Data_inicial_tramit", "model_encoding_type": ModelEncodingType.tabular_datetime},
                {"name" : "Data_fi_del_tramit", "model_encoding_type": ModelEncodingType.tabular_datetime},
                {"name" : "Estat_tramit", "model_encoding_type": ModelEncodingType.tabular_categorical}]
    }

pagamentnomina_table_config = {
    "name": "pagamentnomina",
    "data": df_pagamentnomina,
    "tabular_model_configuration": {
        "max_training_time": 45
    },
    "foreign_keys": [{"column": "pk", "referenced_table": "relationship", "is_context": True}],
    "columns": [{"name" : "pk", "model_encoding_type": ModelEncodingType.auto},
                #{"name" : "Usuari_solicitant", "model_encoding_type": ModelEncodingType.tabular_categorical},
                #{"name" : "N_Expedient", "model_encoding_type": ModelEncodingType.tabular_categorical},
                #{"name" : "N_Procediment", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Data_pagament", "model_encoding_type": ModelEncodingType.tabular_datetime},
                {"name" : "Import", "model_encoding_type": ModelEncodingType.tabular_numeric_digit},
                {"name" : "Tipus_Expedient", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Tipus_procediment", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Municipi", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Provincia", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Ambit_Territorial", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Banc", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Data_nomina", "model_encoding_type": ModelEncodingType.tabular_datetime}]
}

actuacionsnomina_table_config = {
    "name": "actuacionsnomina",
    "data": df_actuacionsnomina,
    "tabular_model_configuration": {
        "max_training_time": 45
    },
    "foreign_keys": [{"column": "pk", "referenced_table": "relationship", "is_context": True}],
    "columns": [{"name" : "pk", "model_encoding_type": ModelEncodingType.auto},
                #{"name" : "identificador", "model_encoding_type": ModelEncodingType.tabular_categorical},
                #{"name" : "N_Expedient", "model_encoding_type": ModelEncodingType.tabular_categorical},
                #{"name" : "N_Procediment", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Efecte_Nomina", "model_encoding_type": ModelEncodingType.tabular_categorical},
                {"name" : "Data_actuacio", "model_encoding_type": ModelEncodingType.tabular_datetime}]
}



# COMMAND ----------

generator_config = {
    "name": "Multi-table Generator",
    "tables": [relationship_table_config, solicitud_table_config, titular_table_config, esborrany_table_config, interopko_table_config, interopok_table_config, fullquery_table_config, pagamentnomina_table_config, actuacionsnomina_table_config]
    #"tables": [relationship_table_config, solicitud_table_config, pagamentnomina_table_config, titular_table_config]
}

# COMMAND ----------

# MAGIC %md
# MAGIC ## Train

# COMMAND ----------

# train a synthetic data generator
generator = mostly.train(name="ctti-tsocial", config=generator_config)
print(f"{generator.id} {generator.name} - {generator.accuracy}")